﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 表7桑园套种农作物情况调查表T_MulInterCrops通用操作类
    /// </summary>
    public class MulInterCropsServices : MulInterCropss
    {
        public Pager<List<MulInterCropsEntity>> GetPageList(MulInterCropsQuery query)
        {
            Pager<List<MulInterCropsEntity>> obj = new Pager<List<MulInterCropsEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_MulInterCrops where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<MulInterCropsEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表7桑园套种农作物情况调查表T_MulInterCrops分页列表失败", ex);
                return obj;
            }
        }
        #region 新的方法
        public ResResult<EditMulInterCropsModel> GetAllList(MulInterCropsQuery query)
        {
            ReportServices reportServices = new ReportServices();
            DictServices dictServices = new DictServices();
            SetListServices setListServices = new SetListServices();
            ResResult<EditMulInterCropsModel> result = new ResResult<EditMulInterCropsModel>();
            result.status = DefaultKey.ErrorResult;
            result.model = new EditMulInterCropsModel();
            ReportEntity reportModel = new ReportEntity();
            if (!string.IsNullOrEmpty(query.FillInYear))
            {
                query.FillInYear = dictServices.GetKey(query.FillInYear);
            }
            reportModel= reportServices.GetReportModel(query.ReportCode, query.AreaCode, query.FillInYear);
            if (reportModel != null)
            {
                if (!string.IsNullOrEmpty(reportModel.Id))
                {
                    result.status = DefaultKey.SuccessResult;
                    result.model.ReportId = reportModel.Id;
                    result.model.Status = reportModel.Status;
                    var list = GetInfo(reportModel.Id).model;
                    if (list.Count > 0)
                    {
                        foreach(var item in list)
                        {
                            if (!string.IsNullOrEmpty(item.SortCode))
                            {
                                item.SortName = setListServices.GetModel("MulInterCrops", item.SortCode).ItemName;
                            }
                        }
                        result.model.ResultList = list;
                        result.message = "数据获取成功";
                    }
                    else
                    {
                        result.message = "数据获取成功";
                    }
                }
                else
                {
                    result.message = "数据获取失败";
                }
            }
            else
            {
                result.message = "该年数据没生成，请联系管理员";
            }
            return result;
        }
        public ResResult<int> AddOrUpdateList(EditMulInterCropsModel model)
        {
            ResResult<int> result = new ResResult<int>();
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();

                var paramlist = new DynamicParameters();
                paramlist.Add("ReportId", model.ReportId);

                string strDelSql = "delete from T_MulInterCrops where ReportId=@ReportId";
                result.model += dbCon.Execute(strDelSql, model.ResultList, trans);

                string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                dbCon.Execute(strRep, new { ReportId = model.ReportId, UpdateTime = DateTime.Now }, trans);

                if (model.ResultList.Count != 0)
                {
                    string strSql = "INSERT INTO T_MulInterCrops";
                    strSql += "(Id";
                    strSql += ",ReportId";
                    strSql += ",SortCode";
                    strSql += ",Area";
                    strSql += ",OutputValue";
                    strSql += ",Remark)";
                    strSql += "VALUES";
                    strSql += "(@Id";
                    strSql += ",@ReportId";
                    strSql += ",@SortCode";
                    strSql += ",@Area";
                    strSql += ",@OutputValue";
                    strSql += ",@Remark)";

                    try
                    {
                        foreach (var item in model.ResultList)
                        {
                            item.Id = CustomFun.GetGuid();
                        }
                        result.model += dbCon.Execute(strSql, model.ResultList, trans);
                        //提交事务
                        trans.Commit();
                        result.status = DefaultKey.SuccessResult;
                        result.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                    catch (Exception ex)
                    {
                        LogHelper.ErrorLog("新增表7桑园套种农作物情况调查表T_MulInterCrops对象信息失败", ex);
                        //出现异常，事务Rollback
                        trans.Rollback();
                        dbCon.Dispose();//数据库连接释放
                        result.status = DefaultKey.ErrorResult;
                        result.message = "获取成功！";

                    }
                }
            }
            return result;
        }
        #endregion
        /// <summary>
        /// 获取填报数据
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns></returns>
        public ResResult<List<MulInterCropsModel>> GetInfo(string reportId)
        {
            ResResult<List<MulInterCropsModel>> obj = new ResResult<List<MulInterCropsModel>>();
            //判定是否生成过报表
            if (CheckRepCount(reportId))
            {
                //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                List<MulInterCropsModel> templist = GetTabContent(reportId, 0);
                try
                {
                    if (AddInfo(templist) > 0)
                    {
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "初始化并且返回数据成功！";
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表7桑园套种农作物情况调查表T_MulInterCrops对象信息失败", ex);
                    obj.message = "返回数据失败！";
                }
            }
            else
            {
                try
                {
                    //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                    List<MulInterCropsModel> templist = GetTabContent(reportId, 1);
                    obj.model = templist;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "返回数据成功！";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表7桑园套种农作物情况调查表T_MulInterCrops对象信息失败", ex);
                    obj.message = "返回数据失败！";
                }
            }
            return obj;
        }

        /// <summary>
        /// 初始化报表内容
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int AddInfo(List<MulInterCropsModel> list)
        {
            //新版使用
            //string strSql = @"INSERT INTO dbo.T_MulInterCrops";
            //strSql += "(";
            //strSql += "Id,";
            //strSql += "ReportId,";
            //strSql += "SortCode,";
            //strSql += "Area,";
            //strSql += "OutputValue,";
            //strSql += "Remark)";
            //strSql += " values(";
            //strSql += "@Id,";
            //strSql += "@ReportId,";
            //strSql += "@SortCode,";
            //strSql += "@Area,";
            //strSql += "@OutputValue,";
            //strSql += "@Remark)";
            string strSql = @"INSERT INTO dbo.T_MulInterCrops";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "SortCode,";
            strSql += "SortName,";
            strSql += "ItemCode,";
            strSql += "ItemName,";
            strSql += "Unit,";
            strSql += "Quantity)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@SortCode,";
            strSql += "@SortName,";
            strSql += "@ItemCode,";
            strSql += "@ItemName,";
            strSql += "@Unit,";
            strSql += "@Quantity)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                try
                {
                    int result = dbCon.Execute(strSql, list);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    LogHelper.ErrorLog("新增表7桑园套种农作物情况调查表T_MulInterCrops对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.ErrorResult;

                }
            }
        }

        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_MulInterCrops where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表7桑园套种农作物情况调查表T_MulInterCrops分页列表失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 获取报表内容
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="edittype">0的时候返回视图列表，不为0的时候返回数据库列表</param>
        /// <returns></returns>
        public List<MulInterCropsModel> GetTabContent(string reportId, int edittype)
        {
            List<MulInterCropsModel> obj = new List<MulInterCropsModel>();
            string strSql = "";
            if (edittype == 0)
            {
                strSql = @"select * from dbo.V_MulInterCrops order by SortCode asc";
            }
            else
            {
                strSql = @"select * from dbo.T_MulInterCrops where ReportId='" + reportId + "' order by SortCode asc";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj = dbCon.Query<MulInterCropsModel>(strSql).ToList();
                    if (edittype == 0)
                    {
                        foreach (var item in obj)
                        {
                            item.ReportId = reportId;
                            item.Id = CustomFun.GetGuid();
                        }
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表6桑园套种农作物情况调查表T_MulInterCrops列表失败", ex);
            }
            return obj;
        }
        /// <summary>
        /// 修改
        /// </summary>
        public ResResult<int> UpdateInfo(List<MulInterCropsEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_MulInterCrops set ";
            strSql += "ReportId = @ReportId,";
            strSql += "SortCode = @SortCode,";
            strSql += "SortName = @SortName,";
            strSql += "ItemCode = @ItemCode,";
            strSql += "ItemName = @ItemName,";
            strSql += "Unit = @Unit,";
            strSql += "Quantity = @Quantity";
            strSql += " where Id = @Id";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    if (list.Count > 0)
                    {
                        string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                        dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    }
                    int result = dbCon.Execute(strSql, list, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    trans.Rollback();
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新表7桑园套种农作物情况调查表T_MulInterCrops对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
        }

        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<MulInterCropsModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<MulInterCropsModel> list = new List<MulInterCropsModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(2);//从第四行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取所有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    MulInterCropsModel model = new MulInterCropsModel();
                    //model.SortCode = row.GetCell(0).ToString();
                    model.SortName = row.GetCell(0).ToString();
                    model.Area = Convert.ToDecimal(row.GetCell(1).ToString());
                    model.OutputValue = Convert.ToDecimal(row.GetCell(2).ToString());
                    model.Remark = row.GetCell(3).ToString();
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 删除
        /// </summary>
        public ResResult<int> DeleteData(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_MulInterCrops where ReportId = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除表7桑园套种农作物情况调查表T_MulInterCrops对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 桑园套种农作物情况调查表统计--------(按年统计)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulInterCropsStatsModel>> GetStatsList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }

            string ProvinceCode = "530000000000";
            ResResult<List<MulInterCropsStatsModel>> result = new ResResult<List<MulInterCropsStatsModel>>();
            /*县级统计代码*/
            string StrAreaSql = "select Area.Code AreaCode, Dict.DictText year, Area.FullName AreaName,Coord";
            StrAreaSql += ",ISNULL(MLSMJ,0.00) MLSMJ,ISNULL(MLSCZ, 0.00) MLSCZ,ISNULL(DDMJ, 0.00) DDMJ,ISNULL(DDCZ, 0.00) DDCZ,ISNULL(CDMJ, 0.00) CDMJ,";
            StrAreaSql += "ISNULL(CDCZ,0.00) CDCZ,ISNULL(LJMJ,0.00) LJMJ,ISNULL(LJCZ,0.00) LJCZ,ISNULL(WDMJ,0.00) WDMJ,";
            StrAreaSql += "ISNULL(WDCZ,0.00) WDCZ,ISNULL(HCMJ,0.00) HCMJ,ISNULL(HCCZ,0.00) HCCZ,ISNULL(QTMJ,0.00) QTMJ,ISNULL(QTCZ,0.00) QTCZ    ";
            StrAreaSql += " from(";
            StrAreaSql += "select ";
            StrAreaSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7001001' THEN Quantity ELSE 0 END) AS MLSMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7001002' THEN Quantity ELSE 0 END) AS MLSCZ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7002001' THEN Quantity ELSE 0 END) AS DDMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7002002' THEN Quantity ELSE 0 END) AS DDCZ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7003001' THEN Quantity ELSE 0 END) AS CDMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7003002' THEN Quantity ELSE 0 END) AS CDCZ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7004001' THEN Quantity ELSE 0 END) AS LJMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7004002' THEN Quantity ELSE 0 END) AS LJCZ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7005001' THEN Quantity ELSE 0 END) AS WDMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7005002' THEN Quantity ELSE 0 END) AS WDCZ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7006001' THEN Quantity ELSE 0 END) AS HCMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7006002' THEN Quantity ELSE 0 END) AS HCCZ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7007001' THEN Quantity ELSE 0 END) AS QTMJ, ";
            StrAreaSql += "SUM(CASE ItemCode WHEN 'C7007001' THEN Quantity ELSE 0 END) AS QTCZ    ";
            StrAreaSql += "from  [dbo].[T_Report] Report left join T_MulInterCrops mc on Report.Id = mc.ReportId ";
            StrAreaSql += "right join T_Area Area on Area.Code = Report.AreaCode ";
            StrAreaSql += "where ";
            StrAreaSql += "Area.ParentCode = @AreaCode  and Report.Status=@ReportStatus and Report.FillInYear=@FillInYear  ";
            StrAreaSql += "group by rollup(Area.Code)) tt1 left join T_Area Area on tt1.Code = Area.Code inner join T_Dict Dict  on Dict.GroupCode='ReportYear' and DictValue=@FillInYear ";
            StrAreaSql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrAreaSql += "order by AreaCode asc ";

            /*市级统计代码*/
            string StrCitySql = "select Area.Code AreaCode,Dict.DictText year, Area.FullName AreaName, Coord, ";
            StrCitySql += "ISNULL(MLSMJ,0.00) MLSMJ,ISNULL(MLSCZ, 0.00) MLSCZ,ISNULL(DDMJ, 0.00) DDMJ,ISNULL(DDCZ, 0.00) DDCZ,ISNULL(CDMJ, 0.00) CDMJ ";
            StrCitySql += ",ISNULL(CDCZ,0.00) CDCZ,ISNULL(LJMJ,0.00) LJMJ,ISNULL(LJCZ,0.00) LJCZ,ISNULL(WDMJ,0.00) WDMJ";
            StrCitySql += ",ISNULL(WDCZ,0.00) WDCZ,ISNULL(HCMJ,0.00) HCMJ,ISNULL(HCCZ,0.00) HCCZ,ISNULL(QTMJ,0.00) QTMJ,ISNULL(QTCZ,0.00) QTCZ    ";
            StrCitySql += "from( select ";
            StrCitySql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7001001' THEN Quantity ELSE 0 END) AS MLSMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7001002' THEN Quantity ELSE 0 END) AS MLSCZ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7002001' THEN Quantity ELSE 0 END) AS DDMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7002002' THEN Quantity ELSE 0 END) AS DDCZ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7003001' THEN Quantity ELSE 0 END) AS CDMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7003002' THEN Quantity ELSE 0 END) AS CDCZ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7004001' THEN Quantity ELSE 0 END) AS LJMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7004002' THEN Quantity ELSE 0 END) AS LJCZ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7005001' THEN Quantity ELSE 0 END) AS WDMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7005002' THEN Quantity ELSE 0 END) AS WDCZ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7006001' THEN Quantity ELSE 0 END) AS HCMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7006002' THEN Quantity ELSE 0 END) AS HCCZ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7007001' THEN Quantity ELSE 0 END) AS QTMJ, ";
            StrCitySql += "SUM(CASE ItemCode WHEN 'C7007001' THEN Quantity ELSE 0 END) AS QTCZ    ";
            StrCitySql += "from t_report t1  ";
            StrCitySql += "left  join T_MulInterCrops mc on mc.ReportId = t1.Id  ";
            StrCitySql += "right  join T_Area Area on Area.Code = t1.AreaCode  ";
            StrCitySql += "where ParentCode!=@AreaCode  and t1.FillInYear=@FillInYear and t1.Status=@ReportStatus ";
            StrCitySql += "group by rollup(Area.ParentCode)  ";
            StrCitySql += ") t left join T_Area  area on area.Code = t.ParentCode  ";
            StrCitySql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear  ";
            StrCitySql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrCitySql += "order by AreaCode asc ";

            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var paramlist = new DynamicParameters();
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    paramlist.Add("ReportStatus", model.Status);
                    List<MulInterCropsStatsModel> list = new List<MulInterCropsStatsModel>();
                    List<MulInterCropsStatsModel> listResult = new List<MulInterCropsStatsModel>();
                    if (model.AreaCode != ProvinceCode)//不为省级用户
                    {
                        list = dbCon.Query<MulInterCropsStatsModel>(StrAreaSql, paramlist).ToList();
                        //foreach (var MulInterCrops in list)
                        //{
                        //    MulInterCrops.TotalArea = MulInterCrops.CDMJ + MulInterCrops.DDMJ + MulInterCrops.HCMJ + MulInterCrops.LJMJ + MulInterCrops.MLSMJ + MulInterCrops.QTMJ + MulInterCrops.WDMJ;
                        //    MulInterCrops.TotalProduct = MulInterCrops.CDCZ + MulInterCrops.DDCZ + MulInterCrops.HCCZ + MulInterCrops.LJCZ + MulInterCrops.MLSCZ + MulInterCrops.QTCZ + MulInterCrops.WDCZ;
                        //    listResult.Add(MulInterCrops);
                        //}
                        //result.model = listResult;
                    }
                    else
                    {
                        list = dbCon.Query<MulInterCropsStatsModel>(StrCitySql, paramlist).ToList();
                    }
                    foreach (var MulInterCrops in list)
                    {
                        MulInterCrops.TotalArea = MulInterCrops.CDMJ + MulInterCrops.DDMJ + MulInterCrops.HCMJ + MulInterCrops.LJMJ + MulInterCrops.MLSMJ + MulInterCrops.QTMJ + MulInterCrops.WDMJ;
                        MulInterCrops.TotalProduct = MulInterCrops.CDCZ + MulInterCrops.DDCZ + MulInterCrops.HCCZ + MulInterCrops.LJCZ + MulInterCrops.MLSCZ + MulInterCrops.QTCZ + MulInterCrops.WDCZ;
                        listResult.Add(MulInterCrops);
                    }
                    List<MulInterCropsStatsModel> list2 = new List<MulInterCropsStatsModel>();
                    //listResult.Where(s => s.AreaCode == model.AreaCode).FirstOrDefault().AreaName = "合计";
                    //MulInterCropsStatsModel m = listResult.Where(s => s.AreaName == "合计").FirstOrDefault();
                    //listResult.Remove(m);
                    //list2.Add(m);
                    //for (var i = 0; i < listResult.Count; i++)
                    //{
                    //    list2.Add(list[i]);
                    //}
                    //result.model = list2;
                    result.model = list;
                    if (list != null)
                    {
                        result.status = DefaultKey.SuccessResult;
                        result.message = "获取桑园套种农作物统计数据成功！";
                    }
                    else
                    {
                        result.status = DefaultKey.InfoResult;
                        result.message = "获取桑园套种农作物统计数据失败！";
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取桑园套种农作物统计数据失败", ex);
            }
            return result;
        }
        public ResResult<List<MulCropsStatistModel>> GetNewStatsList(MulNowModel model)
        {
            string ProvinceCode = "530000000000";
            DictServices dictServices = new DictServices();
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }
            var paramlist = new DynamicParameters();
            paramlist.Add("AreaCode", model.AreaCode);
            paramlist.Add("FillInYear", model.FillInYear);
            paramlist.Add("ReportStatus", model.Status);

            ResResult<List<MulCropsStatistModel>> result = new ResResult<List<MulCropsStatistModel>>();
            //明细
            string detailsSql = "select tpa.Code ParentCode,ta.Code AreaCode,ta.Name AreaName,tr.FillInYear,td.DictText FillInYearName,tc.SortCode";
            detailsSql += ",ts.ItemName SortName,isnull(tc.Area,0) Area,isnull(tc.OutputValue,0) OutputValue,tc.Remark ";
            detailsSql += "FROM T_MulInterCrops tc ";
            detailsSql += "inner join T_Report tr on tr.Id=tc.ReportId ";
            detailsSql += "inner join T_Area ta on ta.Code=tr.AreaCode ";
            detailsSql += "inner join T_Area tpa on tpa.Code=ta.ParentCode ";
            detailsSql += "inner join T_Set_List ts on ts.ItemCode=tc.SortCode and ts.ParentCode='MulInterCrops' ";
            detailsSql += "inner join T_Dict td on td.DictValue=tr.FillInYear and GroupCode='ReportYear' ";
            detailsSql += "where tr.FillInYear=@FillInYear and tr.Status='4' and (tpa.Code=@AreaCode or tpa.ParentCode=@AreaCode or ta.Code=@AreaCode)";
            detailsSql += "order by ta.ParentCode asc,ta.Code asc,tc.SortCode asc ";
            //市级
            string countyTotalSql = "select ta.ParentCode,ta.Code AreaCode,ta.Name AreaName,tr.FillInYear,td.DictText FillInYearName,'heji' SortCode,'合计' SortName";
            countyTotalSql += ",sum(isnull(tc.Area,0)) Area,sum(isnull(tc.OutputValue,0)) OutputValue,'' Remark ";
            countyTotalSql += "from T_Report tr ";
            countyTotalSql += "left join T_Area ta on ta.Code=tr.AreaCode ";
            countyTotalSql += "left join T_Area tpa on tpa.Code=ta.ParentCode ";
            countyTotalSql += "left join T_MulInterCrops tc on tc.ReportId=tr.Id ";
            countyTotalSql += "inner join T_Dict td on td.DictValue=tr.FillInYear and GroupCode='ReportYear' ";
            countyTotalSql += "where tr.FillInYear=@FillInYear and tr.Status='4' and ReportCode='MulInterCrops' and (ta.Code=@AreaCode or ta.ParentCode=@AreaCode or tpa.ParentCode=@AreaCode) ";
            countyTotalSql += "group by tr.AreaCode,tr.FillInYear,tr.[Status],ta.Code,ta.ParentCode,ta.Name,td.DictText ";
            countyTotalSql += "order by ta.ParentCode asc,ta.Code asc";
            //州
            string citySql = "select '530000000000' ParentCode,tpa.Code AreaCode,tpa.Name AreaName,tr.FillInYear,td.DictText FillInYearName,'heji' SortCode,'合计' SortName";
            citySql += ",sum(isnull(tc.Area,0)) Area,sum(isnull(tc.OutputValue,0)) OutputValue,'' Remark ";
            citySql += "from T_Report tr ";
            citySql += "left join T_Area ta on ta.Code=tr.AreaCode ";
            citySql += "left join T_Area tpa on tpa.Code=ta.ParentCode ";
            citySql += "left join T_MulInterCrops tc on tc.ReportId=tr.Id ";
            citySql += "inner join T_Dict td on td.DictValue=tr.FillInYear and GroupCode='ReportYear' ";
            citySql += "where tr.FillInYear=@FillInYear and tr.Status='4' and ReportCode='MulInterCrops' and (tpa.Code=@AreaCode or tpa.ParentCode=@AreaCode) ";
            citySql += "group by tpa.Code,tpa.Name,tr.FillInYear,td.DictText ";
            citySql += "order by tpa.Code asc";
            //全部
            string totalSql = "select '' ParentCode,'530000000000' AreaCode,'云南省' AreaName,tr.FillInYear,td.DictText FillInYearName,'heji' SortCode,'合计' SortName";
            totalSql += ",sum(isnull(tc.Area,0)) Area,sum(isnull(tc.OutputValue,0)) OutputValue,'' Remark ";
            totalSql += "from T_Report tr ";
            totalSql += "left join T_MulInterCrops tc on tc.ReportId=tr.Id ";
            totalSql += "inner join T_Dict td on td.DictValue=tr.FillInYear and GroupCode='ReportYear' ";
            totalSql += "where tr.FillInYear='2' and tr.Status='4' and ReportCode='MulInterCrops' ";
            totalSql += "group by tr.FillInYear,td.DictText";

            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    List<MulCropsStatistModel> list = new List<MulCropsStatistModel>();
                    List<MulCropsStatistModel> detailList = new List<MulCropsStatistModel>();
                    List<MulCropsStatistModel> countyList = new List<MulCropsStatistModel>();
                    List<MulCropsStatistModel> cityList = new List<MulCropsStatistModel>();
                    List<MulCropsStatistModel> totalList = new List<MulCropsStatistModel>();
                    if (model.AreaCode.Equals(ProvinceCode))
                    {
                        totalList = dbCon.Query<MulCropsStatistModel>(totalSql, paramlist).ToList();
                        list.Add(totalList.FirstOrDefault());
                        cityList = dbCon.Query<MulCropsStatistModel>(citySql, paramlist).ToList();

                    }
                    else
                    {
                        cityList = dbCon.Query<MulCropsStatistModel>(citySql, paramlist).ToList();
                    }

                    if (model.IsCity == "1")
                    {
                        countyList = dbCon.Query<MulCropsStatistModel>(countyTotalSql, paramlist).ToList();
                    }
                    if (model.IsDetails == "1")
                    {
                        detailList = dbCon.Query<MulCropsStatistModel>(detailsSql, paramlist).ToList();
                    }
                    if (cityList.Count != 0)
                    {
                        foreach (var item in cityList)
                        {
                            list.Add(item);
                            if (model.IsCity == "1")
                            {
                                foreach (var citem in countyList)
                                {
                                    if (citem.ParentCode == item.AreaCode)
                                    {
                                        list.Add(citem);
                                        if (model.IsDetails == "1")
                                        {
                                            foreach (var ditem in detailList)
                                            {
                                                if (ditem.AreaCode == citem.AreaCode)
                                                {
                                                    list.Add(ditem);
                                                }
                                            }
                                        }
                                    }

                                }
                            }
                        }
                    }
                    else
                    {
                        if (model.IsCity == "1")
                        {
                            foreach (var citem in countyList)
                            {
                                list.Add(citem);

                                if (model.IsDetails == "1")
                                {
                                    foreach (var ditem in detailList)
                                    {
                                        if (ditem.AreaCode == citem.AreaCode)
                                        {
                                            list.Add(ditem);
                                        }
                                    }
                                }
                            }
                        }
                    }




                    result.model = list;
                    result.status = DefaultKey.SuccessResult;
                    result.message = "获取桑园套种农作物统计数据成功！";
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取桑园套种农作物统计数据失败", ex);
            }
            return result;
        }
        /// <summary>
        /// 获取时间趋势图数据列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulInterCropsStatsModel>> GetStatsByTimeList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            List<Select> listDict = dictServices.GetListByGroupCode("ReportYear").model;
            ResResult<List<MulInterCropsStatsModel>> result = new ResResult<List<MulInterCropsStatsModel>>();
            List<MulInterCropsStatsModel> list = new List<MulInterCropsStatsModel>();
            if (listDict.Count > 0)
            {
                if (!string.IsNullOrEmpty(model.AreaCode))
                {
                    for (var i = 0; i < listDict.Count; i++)
                    {
                        model.FillInYear = listDict[i].label;
                        List<MulInterCropsStatsModel> listOne = GetStatsList(model).model;
                        if (listOne.Count > 0)
                        {
                            list.Add(listOne[0]);
                        }
                    }
                    if (list.Count > 0)
                    {
                        result.message = "时间趋势数据列表获取成功";
                        result.model = list.OrderBy(s => s.year).ToList();
                        result.status = DefaultKey.SuccessResult;
                    }
                    else
                    {
                        result.message = "时间趋势数据列表为空";
                        result.status = DefaultKey.InfoResult;
                    }
                }
                else
                {
                    result.message = "时间趋势数据列表失败,当前地区不存在";
                    result.status = DefaultKey.ErrorResult;
                }
            }
            else
            {
                result.message = "时间趋势数据列表失败,时间为空";
                result.status = DefaultKey.ErrorResult;
            }
            return result;
        }
        ///获取统计数据详情
        public ResResult<List<MulInterCropsStatsModel>> GetDetailStats(MulNowModel model)
        {
            ResResult<List<MulInterCropsStatsModel>> res = new ResResult<List<MulInterCropsStatsModel>>();
            List<MulInterCropsStatsModel> list = new List<MulInterCropsStatsModel>();
            string ProvinceCode = DefaultKey.ProvinceCode;
            string FillInYear = model.FillInYear;
            try
            {
                if (model.AreaCode == ProvinceCode)
                {
                    var First = GetStatsList(model).model;
                    //First[0].AreaName = "合计";
                    list.Add(First[0]);//省级汇总合计
                    foreach (var item in First)
                    {
                        if (item.AreaCode != ProvinceCode)
                        {
                            MulNowModel model2 = new MulNowModel();
                            model2.FillInYear = FillInYear;
                            model2.AreaCode = item.AreaCode;
                            model2.Status = model.Status;
                            var Second = GetStatsList(model2).model;
                            list.Add(item);
                            foreach (var item2 in Second)
                            {
                                if (model2.AreaCode != item2.AreaCode)
                                {
                                    list.Add(item2);
                                }
                            }
                        }
                    }
                }
                else
                {
                    list = GetStatsList(model).model;//数据获取
                }
            }
            catch (Exception ex)
            {
                res.model = list;
                res.message = "获取统计数据详情失败" + ex.ToString().Trim();
                res.status = DefaultKey.SuccessResult;
            }
            if (list != null)
            {
                res.model = list;
                res.message = "获取统计数据详情成功";
                res.status = DefaultKey.SuccessResult;
            }
            else
            {
                res.message = "获取统计数据详情成失败";
                res.status = DefaultKey.ErrorResult;
            }
            return res;
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(string ReportId)
        {
            SetListServices setListServices = new SetListServices();
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\Excel.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulInterCrops") as HSSFSheet;//读取模板所在sheet位置
            List<MulInterCropsModel> pagelist = GetInfo(ReportId).model;//数据获取
            HSSFRow row = (HSSFRow)sheet.GetRow(1);
            HSSFRow totalRow = null;
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            decimal? AreaTotal = 0;
            decimal? ValTotal = 0;
            try
            {
                if (pagelist != null)
                {
                    for (int i = 0; i < pagelist.Count; i++)
                    {
                        AreaTotal += pagelist[i].Area;
                        ValTotal += pagelist[i].OutputValue;
                        HSSFRow datarow = (HSSFRow)sheet.GetRow(2 + i);
                        datarow.Cells[0].SetCellValue(setListServices.GetModel("MulInterCrops", pagelist[i].SortCode).ItemName);
                        datarow.Cells[1].SetCellValue(Common.GetOne( pagelist[i].Area));
                        datarow.Cells[2].SetCellValue(Common.GetOne(pagelist[i].OutputValue));
                        if(!string.IsNullOrEmpty(pagelist[i].Remark))
                        {
                            datarow.Cells[3].SetCellValue(pagelist[i].Remark.ToString());
                        }
                        else
                        {
                            datarow.Cells[3].SetCellValue("");
                        }
                        for (int j = 0; j < 3; j++)
                        {
                            datarow.GetCell(j).CellStyle = style;
                        }
                    }
                    totalRow = (HSSFRow)sheet.GetRow(2 + pagelist.Count);
                }
                if (totalRow != null)
                {
                    totalRow.CreateCell(0).SetCellValue("合计");
                    totalRow.CreateCell(1).SetCellValue(Common.GetOne(AreaTotal));
                    totalRow.CreateCell(2).SetCellValue(Common.GetOne(ValTotal));
                    totalRow.CreateCell(3).SetCellValue("");
                }
            }
            catch(Exception ex){ }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 导出统计数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodStats(MulNowModel model)
        {

            string FillInYear = model.FillInYear;
            AreaServices areaServices = new AreaServices();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name == "云南省" ? "云南省_县(市、州)" : areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
            getExcelStatsList(workbook, GetDetailStats(model).model, AreaName);//省级汇总(针对曲线)
            if (model.AreaCode == DefaultKey.ProvinceCode)//判断当前用户是否为省级用户
            {
                AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;
                model.FillInYear = FillInYear;
                getExcelStatsList(workbook, GetStatsList(model).model, AreaName + "_州市");//省级汇总(针对州市)
                var areaList = areaServices.GetPrefectureList();//获取州市级列表
                foreach (var areaCode in areaList)
                {
                    model.AreaCode = areaCode;
                    model.FillInYear = FillInYear;
                    getExcelStatsList(workbook, GetDetailStats(model).model, areaServices.GetByCodeModel(areaCode).model.Name);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 返回excel工作簿
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheet">子表</param>
        /// <param name="model"></param>
        /// <param name="AreaName">地区名称</param>
        /// <returns></returns>
        public HSSFWorkbook getExcelStatsList(HSSFWorkbook workbook, List<MulInterCropsStatsModel> list, string AreaName)
        {
            FileStream file = null;
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\ExcelStats.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulInterCrops") as HSSFSheet;//读取模板所在sheet位置
            //List<VegBasicInfoStatsModel> list = new List<VegBasicInfoStatsModel>();
            //list = null;
            //list = GetDetailStats(model).model;
            #region 县市级表格样式
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            var styleAreaOne = Common.GetOneAreaCellStyle(templateBook, style);
            #endregion
            var styleCityOne = Common.GetOneCityCellStyle(templateBook, style);
            #region 州市级表格样式

            #endregion
            if (list != null)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());
                    datarow.CreateCell(1).SetCellValue(list[i].TotalArea == null ? 0.0 :Convert.ToDouble( list[i].TotalArea));
                    datarow.CreateCell(2).SetCellValue(list[i].TotalProduct == null ? 0.0 : Convert.ToDouble( list[i].TotalProduct));
                    datarow.CreateCell(3).SetCellValue(list[i].MLSMJ == null ? 0.0 : Convert.ToDouble( list[i].MLSMJ));
                    datarow.CreateCell(4).SetCellValue(list[i].MLSCZ == null ? 0.0 : Convert.ToDouble( list[i].MLSCZ));
                    datarow.CreateCell(5).SetCellValue(list[i].DDMJ == null ? 0.0 : Convert.ToDouble( list[i].DDMJ));
                    datarow.CreateCell(6).SetCellValue(list[i].DDCZ == null ? 0.0 : Convert.ToDouble( list[i].DDCZ));
                    datarow.CreateCell(7).SetCellValue(list[i].CDMJ == null ? 0.0 : Convert.ToDouble( list[i].CDMJ));
                    datarow.CreateCell(8).SetCellValue(list[i].CDCZ == null ? 0.0 : Convert.ToDouble( list[i].CDCZ));
                    datarow.CreateCell(9).SetCellValue(list[i].LJMJ == null ? 0.0 : Convert.ToDouble( list[i].LJMJ));
                    datarow.CreateCell(10).SetCellValue(list[i].LJCZ == null ? 0.0 : Convert.ToDouble( list[i].LJCZ));
                    datarow.CreateCell(11).SetCellValue(list[i].WDMJ == null ? 0.0 : Convert.ToDouble( list[i].WDMJ));
                    datarow.CreateCell(12).SetCellValue(list[i].WDCZ == null ? 0.0 : Convert.ToDouble( list[i].WDCZ));
                    datarow.CreateCell(13).SetCellValue(list[i].HCMJ == null ? 0.0 : Convert.ToDouble( list[i].HCMJ));
                    datarow.CreateCell(14).SetCellValue(list[i].HCCZ == null ? 0.0 : Convert.ToDouble( list[i].HCCZ));
                    datarow.CreateCell(15).SetCellValue(list[i].QTMJ == null ? 0.0 : Convert.ToDouble( list[i].QTMJ));
                    datarow.CreateCell(16).SetCellValue(list[i].QTCZ == null ? 0.0 : Convert.ToDouble( list[i].QTCZ));
                    if (CustomFun.IsCity(list[i].AreaCode))
                    {//省级和州市级
                        for (int j = 0; j < 17; j++)
                        {
                            datarow.GetCell(j).CellStyle = styleCityOne;
                        }
                    }
                    else//县级
                    {
                        for (int j = 0; j < 17; j++)
                        {
                            datarow.GetCell(j).CellStyle = styleAreaOne;
                        }
                    }
                }
                sheet.CopyTo(workbook, AreaName, true, true);
            }
            return workbook;
        }
    }
}